﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Web.Mvc;
using System.Data;
using System.Web.Caching;
using System.IO;
using Newtonsoft.Json;
using System.Text.RegularExpressions;
using System.Collections;
using System.Text;
using System.Reflection;
using Common;
using MySql.Data.MySqlClient;
using Newtonsoft.Json.Converters;
using SqlSugar;
using SugarModels;
using System.Web;

namespace Esquel.Canteen.Controllers.Report
{

    public class OnePassJsonBack
    {
        public bool success { get; set; }
        public string errorCode { get; set; }
        public string msg { get; set; }
        public string appVersion { get; set; }
        public string ignoreMsg { get; set; }
        public object body { get; set; }

    }


    /// <summary>
    /// 原材料控制器
    /// </summary>
    public class ReportController  : Controller
    {
        /// <summary>
        /// 原材料控制器
        /// </summary>
        public ReportController()
        {
           

        }

        public ActionResult file_up()
        {
            string pathori = Server.MapPath("/upload/sys/image/");
            string filename = System.Guid.NewGuid().ToString() + ".jpg";
            string path = Path.Combine(pathori, filename);
            string re = "/upload/sys/image/" + filename;
            Request.Files["Fdata"].SaveAs(path);

            string json = Dos.Common.JSON.ToJSON(new { success = true ,path=re});
            return Content(json, "text/json");

        }

        public ActionResult Gg_edit()
        {
            if (Request.IsAjaxRequest() == true)
            {
                string act = Assiweb.GetRequestString("act");
                if (act == "get")
                {
                    int id = Assiweb.GetRequestInt32("id");
                    var db = SqlSugarDb.GetInstance();
                    yd_advertise yd = db.Queryable<yd_advertise>().Where(a => a.id == id).First();
                    string json = Dos.Common.JSON.ToJSON(yd);
                    return Content(json, "text/json");
                }
                else if (act == "save")
                {
                    string data = Assiweb.GetRequestString("data");
                    var item = JsonConvert.DeserializeObject<yd_advertise>(data);
                    var db = SqlSugarDb.GetInstance();
                    if (item.id == 0)
                    {
                        var t3 = db.Insertable<yd_advertise>(item).ExecuteCommand();
                    }
                    else {
                        var t3 = db.Updateable<yd_advertise>(item).UpdateColumns(a => new { a.name, a.adh, a.adw, a.adp, a.href, a.imgurl }).ExecuteCommand();
                    }

                    
                    string json = Dos.Common.JSON.ToJSON(new { success = true });
                    return Content(json, "text/json");
                }
                else if (act == "open")
                {
                    int id = Assiweb.GetRequestInt32("id");
                    int status = Assiweb.GetRequestInt32("status");
                    var db = SqlSugarDb.GetInstance();

                    var t10 = db.Updateable<yd_advertise>()
                    .UpdateColumns(it => new yd_advertise() { is_del = status })
                    .Where(it => it.id == id).ExecuteCommand();

                    string json = Dos.Common.JSON.ToJSON(new { success = true });
                    return Content(json, "text/json");

                }
            }
            return View("Gg_edit");
        }

        public ActionResult Gg()
        {
            string excel = Assiweb.GetRequestString("excel");
            string columns = "";
            //string dbe = Assiweb.GetRequestString("db");
            //string den = Assiweb.GetRequestString("de");
            string name = Assiweb.GetRequestString("name");
            String sql = " select * from yd_advertise where 1=2";
            string where = " where 1=1";
            if (name != "")
            {
                where += " and name like '%" + name + "%' ";
            }
            sql = @"
                select * from yd_advertise " + where + @" order by is_del asc
                    ";

            if (excel == "1")
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql, null);
                columns = Assiweb.GetRequestString("col");
                //ArrayList alcolumns = (ArrayList)JSON.Decode(columns);
                //ExportExcel(alcolumns, dt);
                return null;
            }

            if (Request.IsAjaxRequest() == true)
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql, null);
                IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
                timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";

                string json = Dos.Common.JSON.ToJSON(dt);
                return Content(json, "text/json");
            }
            return View("Gg");
        }


        public ActionResult Jspm()
        {
            string excel = Assiweb.GetRequestString("excel");
            string columns = "";

            string id = Assiweb.GetRequestString("id");
            string type = Assiweb.GetRequestString("type");
            string name = Assiweb.GetRequestString("name");
            String sql = " select name from yd_team where id = " + id;
            string name2 = MysqlHelper.ExecuteFirst(sql, null).ToString();
             sql = " ";
            string where = " ";
            if (type == "day")
            {
                ViewBag.name = name2 + " - 今日步数";
                if (name != "")
                {
                    // 用户名、昵称
                    where = " where ( b.nickname like '%" + name + "%' or c.phone like '%" + name + "%' )";
                }

                string udate = DateTime.Now.ToString("yyyy-MM-dd");
                sql = @"  SELECT  *,(@rownum:=@rownum+1) AS rownum from ( 
                  SELECT a.*,b.nickname,b.imgurl,c.phone from(
                    SELECT a.uid, IFNULL(b.stepday,0) as stepday ,a.step,a.cdate from yd_data a
                      left join(
                        SELECT uid, stepday from yd_data where tid ={2} and udate = '{0}'
                    ) b on a.uid = b.uid where a.tid = {2}
                  ) a
              left join yd_user_gl c on c.uid = a.uid
                  LEFT JOIN yd_user b on a.uid = b.id
  	            {1}
                  ORDER BY stepday desc ,cdate asc
              ) a ,(SELECT @rownum:= 0 ) r   
            ";
                sql = string.Format(sql, udate, where,id);

            }
            else
            {
                ViewBag.name = name2 + " - 个人累计";
                if (name != "")
                {
                    // 用户名、昵称
                    where = " and (nickname like '%" + name + "%' or `yd_user`.phone  like '%" + name + "%' )";
                }

                sql = @" SELECT  *,(@rownum:=@rownum+1) AS rownum from ( SELECT  `yd_data`.`uid`,`yd_data`.`step` as stepday ,`yd_data`.`udate`
                ,`yd_user`.`nickname`,`yd_user`.`imgurl`,`yd_user_gl`.`phone` FROM `yd_data` 
                INNER JOIN `yd_user` ON (`yd_data`.`uid` = `yd_user`.`id`) 
                LEFT OUTER JOIN `yd_user_gl` ON (`yd_data`.`uid` = `yd_user_gl`.`uid`) 
                WHERE (`yd_data`.`tid` = {1} ) {0} ORDER BY `yd_data`.`step` DESC ) a ,(SELECT @rownum:= 0 ) r";
                sql = string.Format(sql, where,id);

            }

            if (excel == "1")
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql, null);
                columns = Assiweb.GetRequestString("col");
                //ArrayList alcolumns = (ArrayList)JSON.Decode(columns);
                //ExportExcel(alcolumns, dt);
                return null;
            }

            if (Request.IsAjaxRequest() == true)
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql, null);
                string json = Dos.Common.JSON.ToJSON(dt);
                return Content(json, "text/json");
            }
            return View("Jspm");
        }


        public ActionResult Js()
        {
            string excel = Assiweb.GetRequestString("excel");
            string columns = "";
            //string dbe = Assiweb.GetRequestString("db");
            //string den = Assiweb.GetRequestString("de");
            string name = Assiweb.GetRequestString("name");
            String sql = " select * from yd_team where 1=2";
            string where = " where 1=1";
            if (name != "")
            {
                where += " and name like '%"+name+"%' ";
            }
            sql = @"
                select * from yd_team " + where + @" order by isjs asc,isgf desc,usernum desc
                    ";
                   
            if (excel == "1")
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql, null);
                columns = Assiweb.GetRequestString("col");
                //ArrayList alcolumns = (ArrayList)JSON.Decode(columns);
                //ExportExcel(alcolumns, dt);
                return null;
            }

            if (Request.IsAjaxRequest() == true)
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql, null);
                IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
                timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";

                string json = Dos.Common.JSON.ToJSON(dt);
                return Content(json, "text/json");
            }
            return View("Js");
        }


        public ActionResult Js_edit()
        {
            if (Request.IsAjaxRequest() == true)
            {
                string act = Assiweb.GetRequestString("act");
                if (act == "get")
                {
                    int id = Assiweb.GetRequestInt32("id");
                    var db = SqlSugarDb.GetInstance();
                    yd_team yd = db.Queryable<yd_team>().Where(a => a.id == id).First();
                    string json = Dos.Common.JSON.ToJSON(yd);
                    return Content(json, "text/json");
                }
                else if (act == "save")
                {
                    string data = Assiweb.GetRequestString("data");
                    var item = JsonConvert.DeserializeObject<yd_team>(data);
                    var db = SqlSugarDb.GetInstance();
                    var t3 = db.Updateable<yd_team>(item).UpdateColumns(a => new { a.name, a.isgf,a.zdorder, a.url }).ExecuteCommand();
                    string json = Dos.Common.JSON.ToJSON(new { success = true });
                    return Content(json, "text/json");
                }
                else if (act == "open")
                {
                    //int id = Assiweb.GetRequestInt32("id");
                    //int status = Assiweb.GetRequestInt32("status");
                    //var db = SqlSugarDb.GetInstance();

                    //var t10 = db.Updateable<yd_team>()
                    //.UpdateColumns(it => new yd_team() { is_del = status })
                    //.Where(it => it.id == id).ExecuteCommand();

                    string json = Dos.Common.JSON.ToJSON(new { success = true });
                    return Content(json, "text/json");

                }
            }
            return View("Js_edit");
        }


        public string DataTableToJson(DataTable table)
        {
            var JsonString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
                JsonString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JsonString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JsonString.Append("}");
                    }
                    else
                    {
                        JsonString.Append("},");
                    }
                }
                JsonString.Append("]");
            }
            return JsonString.ToString();
        }  



        /// <summary>
        /// 索引页
        /// </summary>
        /// <returns></returns>
        [Description("报表-制衣厂员工用餐情况")]
        public ActionResult Zyc()
        {
            string excel = Assiweb.GetRequestString("excel");
            string columns = "";
            string dbe = Assiweb.GetRequestString("db");
            string den = Assiweb.GetRequestString("de");
            string cb = Assiweb.GetRequestString("cb");
            String sql = "select  * from sys_user where 1=2";
            if (dbe != "" && den != "")
            {
                if (cb == "1")
                {
                    sql = @"
SELECT code, name , department from sys_user WHERE id in (
SELECT DISTINCT user_id from ct_order a LEFT JOIN sys_user b  on a.user_id=b.id WHERE
a.dinner_date>='{0}' and a.dinner_date <= '{1}'
AND a.status='1'  AND a.Is_pre='1'  AND a.del_flag=0 AND a.order_type='0' AND b.department like 'GLG%'
) AND status=1 AND factory_id='9a96a9106216453faf44259ee7f98f69' ORDER BY department,code asc
                    ";
                    sql = string.Format(sql, dbe, den);
                }
                else if (cb == "2")
                {
                    sql = @"
SELECT code, name , department from sys_user WHERE id in (
SELECT DISTINCT user_id from ct_order a LEFT JOIN sys_user b  on a.user_id=b.id 
WHERE a.dinner_date>='{0}' and a.dinner_date <= '{1}'
AND a.status='1'  AND a.Is_pre='0'  AND a.del_flag=0 AND a.order_type='0' AND b.department like 'GLG%'
) AND status=1  AND factory_id='9a96a9106216453faf44259ee7f98f69' ORDER BY department,code asc

                ";
                    sql = string.Format(sql, dbe, den);
                }
                else if (cb == "3")
                {
                    sql = @"
SELECT code, name , department from sys_user WHERE id in (
SELECT DISTINCT user_id from ct_order a LEFT JOIN sys_user b  on a.user_id=b.id WHERE 
a.dinner_date>='{0}' and a.dinner_date <= '{1}'
AND a.status='1'   AND a.del_flag=0 AND a.order_type='1' AND b.department like 'GLG%'
) AND status=1 AND factory_id='9a96a9106216453faf44259ee7f98f69'  ORDER BY department,code asc
                ";
                    sql = string.Format(sql, dbe, den);
                }
                else if (cb == "4")
                {
                    sql = @"
                    SELECT code, name , department from sys_user WHERE  status=1 AND factory_id='9a96a9106216453faf44259ee7f98f69' 
                        AND department like 'GLG%' AND id not in (
                    SELECT DISTINCT user_id from ct_order a LEFT JOIN sys_user b  on a.user_id=b.id WHERE 
                    a.dinner_date>='{0}' and a.dinner_date <= '{1}'
                    AND a.del_flag=0 AND a.status='1' AND a.factory_id='9a96a9106216453faf44259ee7f98f69' AND b.department like 'GLG%') 
                    ORDER BY department,code asc
                ";
                    sql = string.Format(sql, dbe, den);
                }
            }

            if (excel == "1")
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql,null);
                columns = Assiweb.GetRequestString("col");
                //ArrayList alcolumns = (ArrayList)JSON.Decode(columns);
                //ExportExcel(alcolumns, dt);
                return null;
            }

            if (Request.IsAjaxRequest() == true)
            {
                DataTable dt = MysqlHelper.ExecuteDataTable(sql, null);
                string json = JsonConvert.SerializeObject(dt);
                return Content(json, "text/json");
            }
            return View("Zyc");
        }

        [Description("报表-查询")]
        public ActionResult Query()
        {
            if (Request.IsAjaxRequest() == true)
            {
                string sql = Request["sql"].Trim();
                //Regex.Replace(sql, "update", " ", RegexOptions.IgnoreCase);
                Regex.Replace(sql, "delete", " ", RegexOptions.IgnoreCase);
                string s = "[{'query':''}]";
                try
                {
                  
                    DataTable dt = new DataTable();
                    if (sql.IndexOf("update") == 0)
                    {
                        
                       
                    }
                    else {
                       
                    }
                    
                }
                catch (Exception ex)
                {
                    var obj = new { err = ex.ToString() };
                    s = "[" + JsonConvert.SerializeObject(obj) + "]";
                }

                return Json(s, JsonRequestBehavior.AllowGet);
            }
            return View("Query");
        }


        /// <summary>
        /// DataTable转化为List集合
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="dt">datatable表</param>
        /// <param name="isStoreDB">是否存入数据库datetime字段，date字段没事，取出不用判断</param>
        /// <returns>返回list集合</returns>
        public static List<T> TableToList<T>(DataTable dt, bool isStoreDB = true)
        {
            List<T> list = new List<T>();
            Type type = typeof(T);
            //List<string> listColums = new List<string>();
            PropertyInfo[] pArray = type.GetProperties(); //集合属性数组
            foreach (DataRow row in dt.Rows)
            {
                T entity = Activator.CreateInstance<T>(); //新建对象实例 
                foreach (PropertyInfo p in pArray)
                {
                    if (!dt.Columns.Contains(p.Name) || row[p.Name] == null || row[p.Name] == DBNull.Value)
                    {
                        continue;  //DataTable列中不存在集合属性或者字段内容为空则，跳出循环，进行下个循环   
                    }
                    if (isStoreDB && p.PropertyType == typeof(DateTime) && Convert.ToDateTime(row[p.Name]) < Convert.ToDateTime("1753-01-01"))
                    {
                        continue;
                    }
                    try
                    {
                        var obj = Convert.ChangeType(row[p.Name], p.PropertyType);//类型强转，将table字段类型转为集合字段类型  
                        p.SetValue(entity, obj, null);
                    }
                    catch (Exception)
                    {
                        // throw;
                    }
                    //if (row[p.Name].GetType() == p.PropertyType)
                    //{
                    //    p.SetValue(entity, row[p.Name], null); //如果不考虑类型异常，foreach下面只要这一句就行
                    //}                    
                    //object obj = null;
                    //if (ConvertType(row[p.Name], p.PropertyType,isStoreDB, out obj))
                    //{                                        
                    //    p.SetValue(entity, obj, null);
                    //}                
                }
                list.Add(entity);
            }
            return list;
        }

        public void ExportExcel(ArrayList columns, DataTable data)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "GB2312";
            //Response.Charset = "UTF-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + "grid" + ".xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
            Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
            string s = ExportTable(data, columns);
            Response.Write(s);
            Response.End();

            //System.Web.HttpContext.Current.Response.Charset = "utf-8";
            //System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("grid.xls", System.Text.Encoding.UTF8).ToString());
            //System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //System.Web.HttpContext.Current.Response.ContentType = "application/x-excel";
            ////StringWriter tw = new StringWriter();
            //string s = ExportTable(data, columns);
            //System.Web.HttpContext.Current.Response.Output.Write(s);
            //System.Web.HttpContext.Current.Response.Flush();
            //System.Web.HttpContext.Current.Response.End();

        }
        public static string ExportTable(DataTable data, ArrayList columns)
        {

            ArrayList columnsBottom = getColumnsBottom(columns);

            ArrayList columnsTable = getColumnsTable(columns);

            StringBuilder sb = new StringBuilder();

            //data += tb.TableName + "\n";
            sb.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">");
            sb.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
            //写出列名

            for (int i = 0; i < columnsTable.Count; i++)
            {
                ArrayList columnsRow = (ArrayList)columnsTable[i];
                sb.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">");
                foreach (Hashtable column in columnsRow)
                {
                    sb.AppendLine("<td colspan=" + column["colspan"] + " rowspan=" + column["rowspan"] + ">" + column["header"] + "</td>");
                }
                sb.AppendLine("</tr>");
            }
            //写出数据

            int count = 0;
            foreach (DataRow row in data.Rows)
            {
                sb.Append("<tr>");
                foreach (Hashtable column in columnsBottom)
                {
                    Object value;
                    if (column["field"] != null)
                    {
                        value = row[column["field"].ToString()];
                    }
                    else
                    {
                        value = "";
                    }
                    if (Convert.ToString(column["type"]) == "indexcolumn") value = count + 1;
                    sb.AppendLine("<td style=\"vnd.ms-excel.numberformat: @;\">" + value + "</td>");
                }
                sb.AppendLine("</tr>");
                count++;
            }
            sb.AppendLine("</table>");


            return sb.ToString();
        }

        public static ArrayList getColumnsBottom(ArrayList columns)
        {
            ArrayList columnsBottom = new ArrayList();

            for (int i = 0; i < columns.Count; i++)
            {
                Hashtable column = (Hashtable)columns[i];

                if (column["columns"] != null)
                {
                    ArrayList childColumns = (ArrayList)column["columns"];
                    columnsBottom.AddRange(getColumnsBottom(childColumns));
                }
                else
                {
                    columnsBottom.Add(column);
                }

            }
            return columnsBottom;
        }

        public static ArrayList getColumnsTable(ArrayList columns)
        {
            ArrayList table = new ArrayList();

            getColumnsRows(columns, 0, table);

            createTableSpan(table);

            return table;

        }

        public static void getColumnsRows(ArrayList columns, int level, ArrayList table)
        {
            ArrayList row = null;
            if (table.Count > level)
            {
                row = (ArrayList)table[level];
            }
            else
            {
                row = new ArrayList();
                table.Add(row);
            }

            for (int i = 0; i < columns.Count; i++)
            {

                Hashtable column = (Hashtable)columns[i];
                ArrayList childColumns = (ArrayList)column["columns"];

                row.Add(column);

                if (childColumns != null)
                {

                    getColumnsRows(childColumns, level + 1, table);
                }

            }
        }

        public static void createTableSpan(ArrayList table)
        {
            for (int i = 0; i < table.Count; i++)
            {
                ArrayList row = (ArrayList)table[i];  //row
                for (int l = 0; l < row.Count; l++)
                {
                    Hashtable cell = (Hashtable)row[l];   //column

                    int colSpan = getColSpan(cell);
                    cell["colspan"] = colSpan;

                    if (colSpan > 1)
                    {
                        cell["rowspan"] = 1;
                    }
                    else
                    {
                        cell["rowspan"] = table.Count - i;
                    }

                }
            }
        }

        public static int getColSpan(Hashtable column)
        {
            int colSpan = 0;
            ArrayList childColumns = (ArrayList)column["columns"];
            if (childColumns != null)
            {
                for (int i = 0; i < childColumns.Count; i++)
                {
                    Hashtable child = (Hashtable)childColumns[i];
                    colSpan += getColSpan(child);
                }
            }
            else
            {
                colSpan = 1;
            }
            return colSpan;
        }

    }
}
